-- ================================
-- AI图文管理助手 - PostgreSQL数据库建表脚本
-- 作者: wangguiyou
-- 创建时间: 2025-01-27
-- 说明: 专门针对PostgreSQL数据库的建表语句
-- ================================

-- 创建数据库（如果需要）
-- CREATE DATABASE ai_image_text_manager WITH ENCODING 'UTF8';

-- 创建images表 - 图片信息表
CREATE TABLE IF NOT EXISTS images (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_url VARCHAR(500) NOT NULL,
    file_size BIGINT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    width INTEGER,
    height INTEGER,
    tags TEXT,
    description TEXT,
    ai_recognition_status VARCHAR(20) DEFAULT 'PENDING' CHECK (ai_recognition_status IN ('PENDING', 'SUCCESS', 'FAILED')),
    ai_recognition_result TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    usage_count INTEGER DEFAULT 0,
    is_deleted BOOLEAN DEFAULT FALSE
);

-- 添加表注释
COMMENT ON TABLE images IS '图片信息表';
COMMENT ON COLUMN images.id IS '图片ID';
COMMENT ON COLUMN images.name IS '图片名称';
COMMENT ON COLUMN images.original_name IS '原始文件名';
COMMENT ON COLUMN images.file_path IS '文件存储路径';
COMMENT ON COLUMN images.file_url IS '文件访问URL';
COMMENT ON COLUMN images.file_size IS '文件大小(字节)';
COMMENT ON COLUMN images.mime_type IS '文件MIME类型';
COMMENT ON COLUMN images.width IS '图片宽度';
COMMENT ON COLUMN images.height IS '图片高度';
COMMENT ON COLUMN images.tags IS '标签列表(JSON格式)';
COMMENT ON COLUMN images.description IS '图片描述';
COMMENT ON COLUMN images.ai_recognition_status IS 'AI识别状态';
COMMENT ON COLUMN images.ai_recognition_result IS 'AI识别结果(JSON格式)';
COMMENT ON COLUMN images.created_at IS '创建时间';
COMMENT ON COLUMN images.updated_at IS '更新时间';
COMMENT ON COLUMN images.usage_count IS '使用次数';
COMMENT ON COLUMN images.is_deleted IS '是否删除';

-- 创建images表索引
CREATE INDEX IF NOT EXISTS idx_images_created_at ON images(created_at);
CREATE INDEX IF NOT EXISTS idx_images_updated_at ON images(updated_at);
CREATE INDEX IF NOT EXISTS idx_images_usage_count ON images(usage_count);
CREATE INDEX IF NOT EXISTS idx_images_ai_status ON images(ai_recognition_status);
CREATE INDEX IF NOT EXISTS idx_images_mime_type ON images(mime_type);
CREATE INDEX IF NOT EXISTS idx_images_is_deleted ON images(is_deleted);

-- 创建全文搜索索引（PostgreSQL特有）
CREATE INDEX IF NOT EXISTS idx_images_tags_gin ON images USING gin(to_tsvector('english', tags));
CREATE INDEX IF NOT EXISTS idx_images_description_gin ON images USING gin(to_tsvector('english', description));

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为images表创建更新时间触发器
CREATE TRIGGER update_images_updated_at BEFORE UPDATE ON images
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建text_contents表 - 文案内容表
CREATE TABLE IF NOT EXISTS text_contents (
    id BIGSERIAL PRIMARY KEY,
    image_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    content_type VARCHAR(20) NOT NULL CHECK (content_type IN ('SHORT', 'LONG')),
    style_type VARCHAR(20) NOT NULL CHECK (style_type IN ('PROMOTIONAL', 'WARM', 'PROFESSIONAL')),
    prompt_used TEXT,
    ai_generation_status VARCHAR(20) DEFAULT 'PENDING' CHECK (ai_generation_status IN ('PENDING', 'SUCCESS', 'FAILED')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE,

    -- 外键约束
    FOREIGN KEY (image_id) REFERENCES images(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE text_contents IS '文案内容表';
COMMENT ON COLUMN text_contents.id IS '文案ID';
COMMENT ON COLUMN text_contents.image_id IS '关联图片ID';
COMMENT ON COLUMN text_contents.content IS '文案内容';
COMMENT ON COLUMN text_contents.content_type IS '文案类型';
COMMENT ON COLUMN text_contents.style_type IS '文案风格';
COMMENT ON COLUMN text_contents.prompt_used IS '使用的提示词';
COMMENT ON COLUMN text_contents.ai_generation_status IS 'AI生成状态';
COMMENT ON COLUMN text_contents.created_at IS '创建时间';
COMMENT ON COLUMN text_contents.updated_at IS '更新时间';
COMMENT ON COLUMN text_contents.is_deleted IS '是否删除';

-- 创建text_contents表索引
CREATE INDEX IF NOT EXISTS idx_text_contents_image_id ON text_contents(image_id);
CREATE INDEX IF NOT EXISTS idx_text_contents_content_type ON text_contents(content_type);
CREATE INDEX IF NOT EXISTS idx_text_contents_style_type ON text_contents(style_type);
CREATE INDEX IF NOT EXISTS idx_text_contents_created_at ON text_contents(created_at);
CREATE INDEX IF NOT EXISTS idx_text_contents_ai_status ON text_contents(ai_generation_status);
CREATE INDEX IF NOT EXISTS idx_text_contents_is_deleted ON text_contents(is_deleted);

-- 创建全文搜索索引
CREATE INDEX IF NOT EXISTS idx_text_contents_content_gin ON text_contents USING gin(to_tsvector('english', content));

-- 为text_contents表创建更新时间触发器
CREATE TRIGGER update_text_contents_updated_at BEFORE UPDATE ON text_contents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建generated_images表 - 图片生成记录表
CREATE TABLE IF NOT EXISTS generated_images (
    id BIGSERIAL PRIMARY KEY,
    original_image_id BIGINT NOT NULL,
    generated_image_id BIGINT NOT NULL,
    generation_prompt TEXT,
    generation_params TEXT,
    ai_generation_status VARCHAR(20) DEFAULT 'PENDING' CHECK (ai_generation_status IN ('PENDING', 'SUCCESS', 'FAILED')),
    similarity_score DECIMAL(3,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE,

    -- 外键约束
    FOREIGN KEY (original_image_id) REFERENCES images(id) ON DELETE CASCADE,
    FOREIGN KEY (generated_image_id) REFERENCES images(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE generated_images IS '图片生成记录表';
COMMENT ON COLUMN generated_images.id IS '生成记录ID';
COMMENT ON COLUMN generated_images.original_image_id IS '原始图片ID';
COMMENT ON COLUMN generated_images.generated_image_id IS '生成图片ID';
COMMENT ON COLUMN generated_images.generation_prompt IS '生成提示词';
COMMENT ON COLUMN generated_images.generation_params IS '生成参数(JSON格式)';
COMMENT ON COLUMN generated_images.ai_generation_status IS 'AI生成状态';
COMMENT ON COLUMN generated_images.similarity_score IS '相似度评分(0-1)';
COMMENT ON COLUMN generated_images.created_at IS '创建时间';
COMMENT ON COLUMN generated_images.updated_at IS '更新时间';
COMMENT ON COLUMN generated_images.is_deleted IS '是否删除';

-- 创建generated_images表索引
CREATE INDEX IF NOT EXISTS idx_generated_images_original_id ON generated_images(original_image_id);
CREATE INDEX IF NOT EXISTS idx_generated_images_generated_id ON generated_images(generated_image_id);
CREATE INDEX IF NOT EXISTS idx_generated_images_created_at ON generated_images(created_at);
CREATE INDEX IF NOT EXISTS idx_generated_images_ai_status ON generated_images(ai_generation_status);
CREATE INDEX IF NOT EXISTS idx_generated_images_similarity ON generated_images(similarity_score);
CREATE INDEX IF NOT EXISTS idx_generated_images_is_deleted ON generated_images(is_deleted);

-- 为generated_images表创建更新时间触发器
CREATE TRIGGER update_generated_images_updated_at BEFORE UPDATE ON generated_images
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建tags表 - 标签表
CREATE TABLE IF NOT EXISTS tags (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    category VARCHAR(50),
    usage_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE
);

-- 添加表注释
COMMENT ON TABLE tags IS '标签表';
COMMENT ON COLUMN tags.id IS '标签ID';
COMMENT ON COLUMN tags.name IS '标签名称';
COMMENT ON COLUMN tags.category IS '标签分类';
COMMENT ON COLUMN tags.usage_count IS '使用次数';
COMMENT ON COLUMN tags.created_at IS '创建时间';
COMMENT ON COLUMN tags.updated_at IS '更新时间';
COMMENT ON COLUMN tags.is_deleted IS '是否删除';

-- 创建tags表索引
CREATE INDEX IF NOT EXISTS idx_tags_name ON tags(name);
CREATE INDEX IF NOT EXISTS idx_tags_category ON tags(category);
CREATE INDEX IF NOT EXISTS idx_tags_usage_count ON tags(usage_count);
CREATE INDEX IF NOT EXISTS idx_tags_created_at ON tags(created_at);
CREATE INDEX IF NOT EXISTS idx_tags_is_deleted ON tags(is_deleted);

-- 为tags表创建更新时间触发器
CREATE TRIGGER update_tags_updated_at BEFORE UPDATE ON tags
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建image_tags表 - 图片标签关联表
CREATE TABLE IF NOT EXISTS image_tags (
    id BIGSERIAL PRIMARY KEY,
    image_id BIGINT NOT NULL,
    tag_id BIGINT NOT NULL,
    confidence DECIMAL(3,2),
    source VARCHAR(20) DEFAULT 'AI' CHECK (source IN ('AI', 'MANUAL')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 外键约束
    FOREIGN KEY (image_id) REFERENCES images(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,

    -- 唯一约束
    UNIQUE(image_id, tag_id)
);

-- 添加表注释
COMMENT ON TABLE image_tags IS '图片标签关联表';
COMMENT ON COLUMN image_tags.id IS '关联ID';
COMMENT ON COLUMN image_tags.image_id IS '图片ID';
COMMENT ON COLUMN image_tags.tag_id IS '标签ID';
COMMENT ON COLUMN image_tags.confidence IS '置信度(0-1)';
COMMENT ON COLUMN image_tags.source IS '标签来源';
COMMENT ON COLUMN image_tags.created_at IS '创建时间';

-- 创建image_tags表索引
CREATE INDEX IF NOT EXISTS idx_image_tags_image_id ON image_tags(image_id);
CREATE INDEX IF NOT EXISTS idx_image_tags_tag_id ON image_tags(tag_id);
CREATE INDEX IF NOT EXISTS idx_image_tags_confidence ON image_tags(confidence);
CREATE INDEX IF NOT EXISTS idx_image_tags_source ON image_tags(source);
CREATE INDEX IF NOT EXISTS idx_image_tags_created_at ON image_tags(created_at);

-- 创建users表 - 用户表
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    nickname VARCHAR(100),
    avatar_url VARCHAR(500),
    status SMALLINT DEFAULT 1 CHECK (status IN (0, 1)),
    last_login_time TIMESTAMP,
    login_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE
);

-- 添加表注释
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.id IS '用户ID';
COMMENT ON COLUMN users.username IS '用户名';
COMMENT ON COLUMN users.password IS '密码';
COMMENT ON COLUMN users.email IS '邮箱';
COMMENT ON COLUMN users.nickname IS '昵称';
COMMENT ON COLUMN users.avatar_url IS '头像URL';
COMMENT ON COLUMN users.status IS '状态(0:禁用,1:启用)';
COMMENT ON COLUMN users.last_login_time IS '最后登录时间';
COMMENT ON COLUMN users.login_count IS '登录次数';
COMMENT ON COLUMN users.created_at IS '创建时间';
COMMENT ON COLUMN users.updated_at IS '更新时间';
COMMENT ON COLUMN users.is_deleted IS '是否删除';

-- 创建users表索引
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);
CREATE INDEX IF NOT EXISTS idx_users_last_login ON users(last_login_time);
CREATE INDEX IF NOT EXISTS idx_users_is_deleted ON users(is_deleted);

-- 为users表创建更新时间触发器
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建system_config表 - 系统配置表
CREATE TABLE IF NOT EXISTS system_config (
    id BIGSERIAL PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT,
    config_type VARCHAR(20) DEFAULT 'STRING' CHECK (config_type IN ('STRING', 'NUMBER', 'BOOLEAN', 'JSON')),
    description VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加表注释
COMMENT ON TABLE system_config IS '系统配置表';
COMMENT ON COLUMN system_config.id IS '配置ID';
COMMENT ON COLUMN system_config.config_key IS '配置键';
COMMENT ON COLUMN system_config.config_value IS '配置值';
COMMENT ON COLUMN system_config.config_type IS '配置类型';
COMMENT ON COLUMN system_config.description IS '配置描述';
COMMENT ON COLUMN system_config.created_at IS '创建时间';
COMMENT ON COLUMN system_config.updated_at IS '更新时间';

-- 创建system_config表索引
CREATE INDEX IF NOT EXISTS idx_system_config_key ON system_config(config_key);
CREATE INDEX IF NOT EXISTS idx_system_config_type ON system_config(config_type);

-- 为system_config表创建更新时间触发器
CREATE TRIGGER update_system_config_updated_at BEFORE UPDATE ON system_config
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 创建标签使用次数更新触发器函数
CREATE OR REPLACE FUNCTION update_tag_usage_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE tags SET usage_count = usage_count + 1, updated_at = CURRENT_TIMESTAMP
        WHERE id = NEW.tag_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE tags SET usage_count = GREATEST(usage_count - 1, 0), updated_at = CURRENT_TIMESTAMP
        WHERE id = OLD.tag_id;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ language 'plpgsql';

-- 创建标签使用次数触发器
CREATE TRIGGER tr_image_tags_update_usage_count
    AFTER INSERT OR DELETE ON image_tags
    FOR EACH ROW EXECUTE FUNCTION update_tag_usage_count();

-- 插入系统默认配置数据
INSERT INTO system_config (config_key, config_value, config_type, description) VALUES
('max_upload_file_size', '10485760', 'NUMBER', '最大上传文件大小(字节)'),
('allowed_file_types', '["image/jpeg","image/png","image/gif","image/webp"]', 'JSON', '允许上传的文件类型'),
('ai_recognition_enabled', 'true', 'BOOLEAN', '是否启用AI图像识别'),
('ai_text_generation_enabled', 'true', 'BOOLEAN', '是否启用AI文案生成'),
('ai_image_generation_enabled', 'true', 'BOOLEAN', '是否启用AI图像生成'),
('default_text_style', 'PROMOTIONAL', 'STRING', '默认文案风格'),
('max_generated_images_per_request', '5', 'NUMBER', '每次请求最大生成图片数量'),
('image_thumbnail_size', '300', 'NUMBER', '图片缩略图尺寸'),
('search_result_page_size', '20', 'NUMBER', '搜索结果分页大小'),
('login_session_timeout', '7200', 'NUMBER', '登录会话超时时间(秒)'),
('password_min_length', '6', 'NUMBER', '密码最小长度'),
('max_login_attempts', '5', 'NUMBER', '最大登录尝试次数'),
('user_registration_enabled', 'true', 'BOOLEAN', '是否允许用户注册')
ON CONFLICT (config_key) DO UPDATE SET
    config_value = EXCLUDED.config_value,
    updated_at = CURRENT_TIMESTAMP;

-- 插入默认管理员用户 (用户名: admin, 密码: admin123)
INSERT INTO users (username, password, email, nickname, status, login_count) VALUES
('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBaYMountjdMKe', 'admin@example.com', '管理员', 1, 0)
ON CONFLICT (username) DO UPDATE SET
    email = EXCLUDED.email,
    nickname = EXCLUDED.nickname,
    updated_at = CURRENT_TIMESTAMP;

-- 插入默认标签数据
INSERT INTO tags (name, category, usage_count) VALUES
('食物', 'general', 0),
('饮品', 'general', 0),
('人物', 'general', 0),
('风景', 'general', 0),
('建筑', 'general', 0),
('动物', 'general', 0),
('植物', 'general', 0),
('交通工具', 'general', 0),
('电子产品', 'product', 0),
('服装', 'product', 0),
('家居用品', 'product', 0),
('运动器材', 'product', 0),
('促销', 'marketing', 0),
('新品', 'marketing', 0),
('限时优惠', 'marketing', 0),
('热销', 'marketing', 0)
ON CONFLICT (name) DO UPDATE SET
    category = EXCLUDED.category,
    updated_at = CURRENT_TIMESTAMP;

-- 插入测试图片数据
INSERT INTO images (
    name, original_name, file_path, file_url, file_size, mime_type,
    width, height, description, ai_recognition_status, ai_recognition_result, usage_count
) VALUES
('sample_food_001.jpg', 'delicious_burger.jpg', '/uploads/sample_food_001.jpg',
 'http://localhost:8080/uploads/sample_food_001.jpg', 245760, 'image/jpeg', 800, 600,
 '美味的汉堡包，配有新鲜蔬菜和酱料', 'SUCCESS',
 '{"tags": ["食物", "汉堡", "快餐", "美食"], "confidence": 0.95, "objects": [{"name": "汉堡", "confidence": 0.98}]}', 5),
('sample_drink_001.jpg', 'coffee_cup.jpg', '/uploads/sample_drink_001.jpg',
 'http://localhost:8080/uploads/sample_drink_001.jpg', 189432, 'image/jpeg', 600, 800,
 '精美的咖啡杯，热气腾腾的拿铁咖啡', 'SUCCESS',
 '{"tags": ["饮品", "咖啡", "拿铁", "热饮"], "confidence": 0.92, "objects": [{"name": "咖啡杯", "confidence": 0.96}]}', 3),
('sample_product_001.jpg', 'smartphone.jpg', '/uploads/sample_product_001.jpg',
 'http://localhost:8080/uploads/sample_product_001.jpg', 312456, 'image/jpeg', 1000, 800,
 '最新款智能手机，黑色外观，现代设计', 'SUCCESS',
 '{"tags": ["电子产品", "手机", "智能手机", "科技"], "confidence": 0.89, "objects": [{"name": "手机", "confidence": 0.94}]}', 8)
ON CONFLICT DO NOTHING;

-- 插入测试文案数据
INSERT INTO text_contents (
    image_id, content, content_type, style_type, prompt_used, ai_generation_status
) VALUES
(1, '🍔 美味汉堡，新鲜出炉！', 'SHORT', 'PROMOTIONAL', '为美味汉堡生成促销型短句文案', 'SUCCESS'),
(1, '精选优质牛肉，搭配新鲜蔬菜和特制酱料，每一口都是满满的幸福感。', 'LONG', 'PROMOTIONAL', '为美味汉堡生成促销型长句文案', 'SUCCESS'),
(2, '☕ 醇香拿铁，温暖你的每一天', 'SHORT', 'WARM', '为咖啡生成温暖型短句文案', 'SUCCESS'),
(3, '📱 科技新品，限时特惠！', 'SHORT', 'PROMOTIONAL', '为智能手机生成促销型短句文案', 'SUCCESS');

-- 插入图片标签关联数据
INSERT INTO image_tags (image_id, tag_id, confidence, source) VALUES
(1, (SELECT id FROM tags WHERE name = '食物' LIMIT 1), 0.98, 'AI'),
(2, (SELECT id FROM tags WHERE name = '饮品' LIMIT 1), 0.96, 'AI'),
(3, (SELECT id FROM tags WHERE name = '电子产品' LIMIT 1), 0.94, 'AI')
ON CONFLICT (image_id, tag_id) DO NOTHING;

-- 创建视图：图片详细信息视图
CREATE OR REPLACE VIEW v_image_details AS
SELECT
    i.id,
    i.name,
    i.original_name,
    i.file_path,
    i.file_url,
    i.file_size,
    i.mime_type,
    i.width,
    i.height,
    i.description,
    i.ai_recognition_status,
    i.created_at,
    i.updated_at,
    i.usage_count,
    STRING_AGG(DISTINCT t.name, ',' ORDER BY t.name) as tag_names,
    (SELECT COUNT(*) FROM text_contents tc WHERE tc.image_id = i.id AND tc.is_deleted = FALSE) as text_content_count,
    (SELECT COUNT(*) FROM generated_images gi WHERE gi.original_image_id = i.id AND gi.is_deleted = FALSE) as generated_image_count
FROM images i
LEFT JOIN image_tags it ON i.id = it.image_id
LEFT JOIN tags t ON it.tag_id = t.id AND t.is_deleted = FALSE
WHERE i.is_deleted = FALSE
GROUP BY i.id, i.name, i.original_name, i.file_path, i.file_url, i.file_size,
         i.mime_type, i.width, i.height, i.description, i.ai_recognition_status,
         i.created_at, i.updated_at, i.usage_count;

-- 创建存储过程：更新图片使用次数
CREATE OR REPLACE FUNCTION update_image_usage_count(p_image_id BIGINT)
RETURNS VOID AS $$
BEGIN
    UPDATE images
    SET usage_count = usage_count + 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_image_id;
END;
$$ LANGUAGE plpgsql;

-- 显示创建完成信息
SELECT 'PostgreSQL database schema created successfully!' as message;
SELECT 'Tables created: images, text_contents, generated_images, tags, image_tags, system_config' as info;
SELECT 'View created: v_image_details' as view_info;
SELECT 'Triggers and functions created for automatic updates' as trigger_info;
SELECT 'Test data inserted successfully!' as data_info;
